# -*- coding: utf-8 -*-
"""
Created on Thu Sep 29 09:34:49 2022

@author: Lu_Cool
"""


def remove_redundant_symbols(DataFrame, columns):
    import re
    import pandas as pd
    clean_list = list(map(lambda x: re.sub(
        '[^a-zA-Z\u4e00-\u9fa5|-]+', '', x, re.DOTALL), DataFrame[columns].tolist()))
    DataFrame[columns] = pd.Series(clean_list)
    return DataFrame


def main():
    # 导入第三方库
    import pandas as pd
    import numpy as np
    import re
    # 读取文件
    df = pd.read_excel(read_path)
    # 处理file_path列的数据,去掉多余符号

    df['file_path1_sup'] = pd.Series(list(map(lambda x: x.replace(
        'E:\\Data\\File\\', '').replace('.pdf', ''), df['file_path1'])))
    df['file_path2_sup'] = pd.Series(list(map(lambda x: x.replace(
        'E:\\Data\\File\\', '').replace('.pdf', ''), df['file_path2'])))

    # df = remove_redundant_symbols(df, 'file_path1')
    # df = remove_redundant_symbols(df, 'file_path2')
    # 1.每一列汇总信息
    # df["summary"] = df["groups_nums"].map(str) + "|" + df["FIRM"] + "|" + df["IB1"] + "|" + df["IB2"] + "|" + df["similarity_ratio"].map(str)
    df["summary"] = df.loc[:, ['groups_nums', 'FIRM', 'IB1', 'IB2', 'similarity_ratio',
                               'file_path1_sup', 'file_path2_sup']].apply(lambda x: "|".join(x[1:].map(str)), axis=1)
    # 2.遍历所有组
    # 用于存储所有相似信息的汇总列表
    firm_list = []
    # 获取所有组编号
    group_nums = list(set(df["groups_nums"]))
    for group_num in group_nums:
        group_df = df[df["groups_nums"] == group_num]
        name_set = list(set(group_df["file_path1_sup"]).union(
            set(group_df["file_path2_sup"])))
        # 3.遍历组内ib，计算similarity的汇总
        for name in name_set:
            if len(group_df) >= 3:
                ''
                # 组内数量小于3的视为异常值处理
                similarity_sum = group_df[group_df["summary"].str.findall(name).map(
                    lambda x: True if len(x) != 0 else False)]["similarity_ratio"].sum()
            else:
                # 将异常数据的similarity设置为-1
                similarity_sum = -1
            ib = re.findall('-(.*?)-', name, re.DOTALL)[0]
            firm_list.append("|".join(
                [str(group_num), group_df["FIRM"].iloc[0], ib, name, str(similarity_sum)]))

    # 和原数据进行组合
    new_df = pd.DataFrame(firm_list)[0].str.split("|", expand=True)
    # 组内进行排序
    new_df.columns = ["group_nums", "firm", "ib", "path", "similarity_sum"]
    new_df["similarity_sum"] = new_df["similarity_sum"].map(np.float64)
    new_df["rank"] = new_df.groupby(by="group_nums").rank().iloc[:, -1]
    # 存储文件
    new_df.to_excel(save_path)


if __name__ == "__main__":
    # read_path = r"E:\Data\with_number_txt_result.xlsx"
    read_path = r"E:\Data\without_number_txt_result.xlsx"
    save_path = r"E:\Data\similarity_ranking_rst.xlsx"
    main()
